"""
这个类封装了使用pyodbc对SQL SERVER数据库的直接操作
"""

from util import ip
import pyodbc
from contextlib import contextmanager
import logging

# 配置基本日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

class SQLServer_pyodbc:
    def __init__(self,database):
        self.server = ip
        self.database = database
        self.username = 'sa'
        self.password = 'cj126414.'
        self.conn = None

    """
    连接数据库
    """

    @contextmanager
    def connect(self):
        conn_str = (
            f'DRIVER=ODBC Driver 17 for SQL Server;SERVER={self.server};DATABASE={self.database};UID={self.username};PWD={self.password};')
        self.conn = pyodbc.connect(conn_str)
        self.conn.autocommit = True
        try:
            yield self.conn
        finally:
            self.conn.close()

    """
    这个方法用于获取SQL查询的所有结果。它返回一个包含所有行的列表，每一行是一个包含列值的元组。
    如果查询没有结果，它会返回一个空列表。这个方法通常用于执行返回多行结果的查询，
    例如SELECT * FROM table
    """

    def Query_all(self, sql, params=None):
        with self.connect() as conn:
            with conn.cursor() as cursor:
                if params is None:
                    cursor.execute(sql)
                else:
                    cursor.execute(sql, params)
                rows = cursor.fetchall()
        return rows

    """
    这个方法用于获取SQL查询的第一个结果。它返回一个包含列值的元组。
    如果查询没有结果，它会返回None。
    这个方法通常用于执行只返回一行结果的查询，例如SELECT * FROM table WHERE id = 1
    """

    def Query_one(self, sql, params=None):
        with self.connect() as conn:
            with conn.cursor() as cursor:
                if params is None:
                    cursor.execute(sql)
                else:
                    cursor.execute(sql, params)
                row = cursor.fetchone()
        return row

    """
       执行单条SQL语句，插入、更新、删除等操作
       使用方法，如果有参数，params传入一个元组，否则不需要传入params
       db.execute("insert into Users(Username,Pwd,FullName) values(?,?,?)",('users4','123456','test4'))
       """

    def execute(self, sql, params=None):
        with self.connect() as conn:
            with conn.cursor() as cursor:
                try:
                    if params is None:
                        cursor.execute(sql)
                        rows_affected = cursor.rowcount
                    else:
                        cursor.execute(sql, params)
                        rows_affected = cursor.rowcount
                    conn.commit()  # 显式提交事务
                    return rows_affected
                except Exception as e:
                    conn.rollback()  # 出错时回滚
                    raise e

    """
    批量插入
    调用方法：要批量的数据要放在params_list列表中，每个元素是一个元组，元组的长度和sql语句中？的数量一致
    insert_many("insert into Users(Username,Pwd,FullName) values(?,?,?)",[('users1','123456','test1'),('users2','123456','test2'),('users3','123456','test3')])
    """

    def insert_many(self, sql, params_list):
        with self.connect() as conn:
            with conn.cursor() as cursor:
                cursor.fast_executemany = True
                cursor.executemany(sql, params_list)

    """
    执行多条SQL语句，插入、更新、删除等操作，如果执行失败，会回滚
    """
    def execute_transaction(self, sql_statements: list) -> bool:
        """
        执行一组SQL语句作为一个事务
        
        Args:
            sql_statements: 要执行的SQL语句列表
            
        Returns:
            操作是否成功
        """
        if not sql_statements:
            return True  # 没有语句需要执行，视为成功
        
        try:
            with self.connect() as conn:
                cursor = conn.cursor()
                
                # 关闭自动提交
                conn.autocommit = False
                
                # 执行每条SQL语句
                for sql in sql_statements:
                    cursor.execute(sql)
                
                # 提交事务
                conn.commit()
                return True
        except Exception as e:
            # 记录错误，回滚会在contextmanager中自动处理
            logging.error(f"事务执行失败: {str(e)}, SQL: {sql_statements}")
            return False

